#! /usr/bin/env python
# _*_ coding:UTF-8 _*_
"""
@author:zhh
@Software:PyCharm
@blogs:https://blog.csdn.net/smilehaoh
@time:2020/4/2

"""
from common.connect_db import MyDB

my_db = MyDB()


class Order:
    @staticmethod
    def get_old_totalorder():
        """
        查询老系统线上线下总订单数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where createtime>1586793600  "
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_oneorder(type):
        """
        查询老系统线上订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where ordertype='%s' and recharge=0 and deleted=0 and createtime>1586793600" % type
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_bdorder():
        """
        查询老系统报单订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where ordertype=99 and recharge=0 and deleted=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_yfkorder():
        """
        查询老系统已付款订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where ordertype in (0,2) and recharge=0 and deleted=0 and createtime>1586793600 and status=1"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_yfhorder():
        """
        查询老系统已发货订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where ordertype in (0,2) and recharge=0 and deleted=0 and createtime>1586793600 and status=2"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_ypjwcorder():
        """
        查询老系统已评价和交易完成订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where ordertype in (0,2) and recharge=0 and deleted=0 and createtime>1586793600 and status=3"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_declorder():
        """
        查询老系统线下报单总数
        :return:
        """
        #  sql1 = "select count(*) as totaldeclorder from  ims_ewei_shop_store_submit_order where status in (0,1) and deleted=0 order by ordersn desc"
        sql1 = "SELECT count(1) as totaldeclar from ims_ewei_shop_store_submit_order suborder, ims_ewei_shop_store store,ims_ewei_shop_order sorder where  sorder.ordersn=suborder.ordersn and store.id = suborder.storeid and suborder.status in (0,1) and suborder.deleted=0 AND sorder.ordertype=99"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_offorder():
        """
        查询老系统线下订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where createtime>1586793600 and ordertype=1 and recharge=0 and deleted=0 and status in (1,2,3)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_oneorder2():
        """
        积分商品订单
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where createtime>1586793600 and ordertype=2 and recharge=0 and status in (1,2,3) and deleted=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_oneorder1():
        """
        查询老系统线上订单总数
        :return:
        """
        sql = "select count(storename) as storename FROM `ims_ewei_shop_order_goods` LEFT JOIN `ims_ewei_shop_order` ON `ims_ewei_shop_order`.`id` = `ims_ewei_shop_order_goods`.`orderid`LEFT JOIN `ims_ewei_shop_store` ON `ims_ewei_shop_store`.`id` = `ims_ewei_shop_order`.`storeid`LEFT JOIN `ims_ewei_shop_member` ON `ims_ewei_shop_member`.`id` = `ims_ewei_shop_order`.`mid`LEFT JOIN `ims_ewei_shop_goods` ON `ims_ewei_shop_goods`.`id` = `ims_ewei_shop_order_goods`.`goodsid`WHERE`ims_ewei_shop_order`.`createtime` >= 1586793600 AND `ims_ewei_shop_order`.`ordertype` IN ( 0, 2 ) AND `ims_ewei_shop_order`.`recharge` = 0 AND `ims_ewei_shop_order`.`deleted` = 0 AND `ims_ewei_shop_order`.`status` IN ( 1, 2, 3 ) and ims_ewei_shop_order_goods.id in (select max(id) from ims_ewei_shop_order_goods group by orderid)"
        result = my_db.execute_select1(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_oneorder3():
        """
        查询老系统线上自营订单总数
        :return:
        """
        sql1 = "select count(*)  from ims_ewei_shop_order where storeid in (select id from ims_ewei_shop_store where is_self_support=1) and createtime>1586793600 and ordertype in (0,2) and recharge=0 and status in (1,2,3)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_alipayorder():
        """
        查询老系统支付宝扫码订单总数
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where createtime>1586793600 and ordertype=1 and small_program_type=1 and status in (1,2,3) and deleted=0"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_onorderstatus(status):
        """
        查询老系统线上订单各种状态
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order where createtime>1586793600 and ordertype=0 and status='%s'" % status
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_onorderstatusrefund(status):
        """
        查询老系统线上逆向订单各种状态
        退款状态：-2：用户取消退款；-1 商家拒绝退款；0:暂不处理；1：申请中； 2：商家已同意用户等待发货； 3：用户已发货等待商家收货；4：商家已收货等待退款；5：退款中 6：退款完成
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order_refund where createtime>1586793600 and refundstatus='%s'" % status
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_onorderstatusrefundtotal():
        """
        查询老系统线上总售后订单
        :return:
        """
        sql1 = "SELECT count(*) as oldtotalorder from ims_ewei_shop_order_refund where refundstatus in (0,1,2,3,4,5)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_maintorder(type):
        """
        查询新系统主订单
        :return:
        """
        sql = "select count(*) as mainorder from life_order_order where type='%s' and created_at>'2020/4/14 0:0:0' and status!=-10  and status!=10 " % type
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_maininvestorder():
        """
        查询新系统投资主订单
        :return:
        """
        sql = "select count(*) as mainorder from life_order_order where type='investment' "
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_newonorder():
        """
        查询新系统线上订单总数
        :return:
        """
        sql = "select count(*) as totalorder from life_online_orders where created_at>'2020/4/14 0:0:0'"
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_newofforder():
        """
        查询新系统线下订单总数
        :return:
        """
        sql = "select count(*) as totalorder from life_offline_scan_code_orders where created_at>'2020/4/14 0:0:0' and pay_status=1"
        result = my_db.execute_select(select_db='new', db_name="offorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_newonorder1():
        """
        查询新系统线上自营订单总数
        :return:
        """
        sql = "select count(*) from life_online_orders where shop_id in (select id from onshop.life_online_shop_shops where self_id is not null) and created_at>'2020/4/14 0:0:0'"
        result = my_db.execute_select1(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_maintorder1(type):
        """
        查询新系统主订单
        :return:
        """
        sql = "select count(*) as mainorder from life_order_order where type='%s' and status=20" % type
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_totalorder(type):
        """
        查询新系统主订单某种订单总数
        :return:
        """
        sql = "select count(*) as maintotal from life_order_order where created_at>1586793600 and type='%s'" % type
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_investorder():
        """
        投资新系统订单
        :return:
        """
        sql = "select count(*) as maintotal from life_order_order where type='investment'"
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_declarorder():
        """

        :return:
        """
        sql = "select count(*) as maintotal from life_order_order where type='declaration_order'"
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_oilorder():
        """
        查询新系统油卡订单总数
        :return:
        """
        sql = "select count(*) as maintotal from life_order_order where type='oil'"
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_totaloilorder():
        """
        查询新系统油卡订单总数
        :return:
        """
        sql = "select count(*) as maintotal from life_order_order where  type='oil'"
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderstatus(status):
        """
        查询新系统线上正向订单各种状态
        订单状态：-10 订单关闭 10待付款 15支付中 20已付款 30已发货 40待评价（已结算） 50交易完成
        :return:
        """
        sql = "select count(*) as mainorder from life_order_order where type='onorder' and created_at>'2020/4/14 0:0:0' and status='%s'" % status
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        # result = my_db.execute_select(select_db='new', db_name="ceshi", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderdfk():
        """
        查询新系统线上正向订单各种状态
        订单状态：-10 订单关闭 10待付款 15支付中 20已付款 30已发货 40待评价（已结算） 50交易完成
        :return:
        """
        sql = "select count(*) as mainorder from life_online_orders where status=20 and created_at>'2020/4/14 0:0:0' "
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        # result = my_db.execute_select(select_db='new', db_name="ceshi", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderyfh():
        """
        查询新系统线上正向订单各种状态
        订单状态：-10 订单关闭 10待付款 15支付中 20已付款 30已发货 40待评价（已结算） 50交易完成
        :return:
        """
        sql = "select count(*) as mainorder from life_online_orders where status=30 and created_at>'2020/4/14 0:0:0' "
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        # result = my_db.execute_select(select_db='new', db_name="ceshi", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderyjs():
        """
        查询新系统线上正向订单各种状态
        订单状态：-10 订单关闭 10待付款 15支付中 20已付款 30已发货 40待评价（已结算） 50交易完成
        :return:
        """
        sql = "select count(*) as mainorder from life_online_orders where status in(40,50) and created_at>'2020/4/14 0:0:0' "
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderstatusrefund(status):
        """
        查询新系统线上逆向订单各种状态
        售后状态：10申请中  20退款中 30完成 -10退款关闭 -20 财务驳回
        :return:
        """
        sql = "select count(*) as mainorder from life_online_order_after_sale where created_at>'2020/4/14 0:0:0' and status='%s'" % status
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderstatusrefund1():
        """
        查询新系统线上逆向订单各种状态
        售后状态：10申请中  20退款中 30完成 -10退款关闭 -20 财务驳回
        :return:
        """
        sql = "select count(*) as mainorder from life_online_order_after_sale"
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_onorderstatusrefundtoal():
        """
        新系统线上售后订单总数
        :return:
        """
        sql = "select count(*) as main from life_order_refund where created_at>'2020/4/14 0:0:0' "
        result = my_db.execute_select(select_db='new', db_name="order", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_creditorder():
        """
        查询新系统积分商品订单
        :return:
        """
        sql = "select count(*) as creditorder from life_online_orders where goods_type=2 and created_at>'2020/4/14 0:0:0' "
        result = my_db.execute_select(select_db='new', db_name="onorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_alipayorder():
        """
        查询新系统支付宝扫码订单数量
        :return:
        """
        sql = "select count(*) as alipayorder from life_offline_scan_code_orders where scan_type=4 and created_at>'2020/4/14 0:0:0'"
        result = my_db.execute_select(select_db='new', db_name="offorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_declaration_order():
        """
        查询新系统线下报单订单总数
        :return:
        """
        sql = "select count(*) as declaration_order from life_offline_order_declaration_orders"
        result = my_db.execute_select(select_db='new', db_name="offorder", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_air_order_amount():
        """
        老系统机票总订单数
        :return:
        """
        sql = "select count(id) as count from ims_ewei_air_order where status in (1,2,3) and createtime>=1586793600"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        return result[0]

    @staticmethod
    def get_old_hotel_order_amount():
        """
        老系统酒店总订单数
        :return:
        """
        sql = "select count(id) as count from ims_ewei_hotel_order " \
              "where createtime>=1586793600 and deleted=0 and `showstatus` in (1,2) and resource_name='DF'"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        return result[0]

    @staticmethod
    def get_old_order_success_amount():
        """
        话费充值成功的订单数量
        :return:
        """
        sql = "select count(id) as count from ims_ewei_recharge_order " \
              "where type=0 and create_time>=1586793600 and status=3"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        return result[0]

    @staticmethod
    def get_old_carorder_amount():
        """
        总汽车订单数
        :return:
        """
        sql = "select count(*) as count from ims_car_order"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        return result

    @staticmethod
    def get_1():
        """
        :return:
        """
        sql = "select ordersn from ims_ewei_shop_order where ordertype=99 order by ordersn desc"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        return result


if __name__ == '__main__':
    order = Order()
    o2 = order.get_old_declorder()
